﻿using System;
using System.Collections.Generic;
using System.Windows.Forms;
using FirebirdSql.Data.FirebirdClient;

namespace Administracja
{
    static class Baza
    {
        public static string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = "User ID=sysdba;Password=masterkey;Database=localhost:" + Application.StartupPath + "\\Baza\\" + value; }
        }
        public static string ConnectionStringData = "User ID=sysdba;Password=masterkey;Database=localhost:" + Application.StartupPath + "\\Baza\\DATA.FDB";

        private static string _connectionString;
        private static FbConnection _connectionPriv;
        private static FbConnection _connectionPrivData;
        private static FbDataReader _myreader;
        private static FbTransaction _transaction;
        private static bool _polaczony;

        static Baza()
        {
            //Tymczasowo DATA.FDB, żeby miał jakakolwiek baze. Po uruchomieniu glownego okna ustawi sie wlasciwa.
            ConnectionString = "DATA.FDB";
            OpenConnection();
        }

        public static void OpenConnection()
        {
            if (!_polaczony)
            {
                try
                {
                    _connectionPriv = new FbConnection(ConnectionString);
                    _connectionPriv.Open();

                    _connectionPrivData = new FbConnection(ConnectionStringData);
                    _connectionPrivData.Open();

                    _polaczony = true;
                }
                catch (Exception ex)
                {
                    Wyjatki.Error(ex);
                }
            }
        }

        public static void CloseConnection()
        {
            if (_polaczony)
            {
                try
                {
                    _connectionPriv.Close();
                    _connectionPrivData.Close();

                    _polaczony = false;
                }
                catch (Exception ex)
                {
                    Wyjatki.Error(ex);
                }
            }
        }

        public static void Zapisz(string sqlCommandText)
        {
            try
            {
                Raport.Instance.Raportuj("Zapisz = " + sqlCommandText);

                _transaction = _connectionPriv.BeginTransaction();
                new FbCommand(sqlCommandText, _connectionPriv, _transaction).ExecuteNonQuery();
                _transaction.Commit();
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
                _transaction.Rollback();
            }
        }

        public static void Zapisz(params string[] sqlCommandText)
        {
            try
            {
                _transaction = _connectionPriv.BeginTransaction();

                foreach (string sqlCommand in sqlCommandText)
                {
                    Raport.Instance.Raportuj("ZapiszParams = " + sqlCommand);
                    new FbCommand(sqlCommand, _connectionPriv, _transaction).ExecuteNonQuery();
                }
                _transaction.Commit();
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
                _transaction.Rollback();
            }
        }

        public static void ZapiszData(string sqlCommandText)
        {
            try
            {
                Raport.Instance.Raportuj("ZapiszData = " + sqlCommandText);

                _transaction = _connectionPrivData.BeginTransaction();
                new FbCommand(sqlCommandText, _connectionPrivData, _transaction).ExecuteNonQuery();
                _transaction.Commit();
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
                _transaction.Rollback();
            }
        }

        public static string Wczytaj(string sqlCommandText)
        {
            string ret = "";

            try
            {
                Raport.Instance.Raportuj("Wczytaj = " + sqlCommandText);

                _myreader = new FbCommand(sqlCommandText, _connectionPriv).ExecuteReader();
                while (_myreader.Read())
                {
                    ret = _myreader[0].ToString();
                }
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
            }
            finally
            {
                _myreader.Close();
            }

            if (string.IsNullOrWhiteSpace(ret)) ret = "0";

            return ret;
        }

        public static List<string> Wczytaj1D(string sqlCommandText)
        {
            var ret = new List<string>();

            try
            {
                Raport.Instance.Raportuj("Wczytaj1D = " + sqlCommandText);

                _myreader = new FbCommand(sqlCommandText, _connectionPriv).ExecuteReader();
                while (_myreader.Read())
                {
                    ret.Clear();
                    for (int i = 0; i < _myreader.FieldCount; i++)
                    {
                        ret.Add(_myreader[i].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
            }
            finally
            {
                _myreader.Close();
            }

            return ret;
        }

        public static List<List<string>> Wczytaj2D(string sqlCommandText)
        {
            var ret = new List<List<string>>();

            try
            {
                Raport.Instance.Raportuj("Wczytaj2D = " + sqlCommandText);

                _myreader = new FbCommand(sqlCommandText, _connectionPriv).ExecuteReader();
                while (_myreader.Read())
                {
                    ret.Add(new List<string>());
                    for (int i = 0; i < _myreader.FieldCount; i++)
                    {
                        ret[ret.Count - 1].Add(_myreader[i].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
            }
            finally
            {
                _myreader.Close();
            }

            return ret;
        }

        public static List<string> Wczytaj1Dp(string sqlCommandText)
        {
            var ret = new List<string>();

            try
            {
                Raport.Instance.Raportuj("Wczytaj1Dp = " + sqlCommandText);

                _myreader = new FbCommand(sqlCommandText, _connectionPriv).ExecuteReader();
                while (_myreader.Read())
                {
                    ret.Add(_myreader[0].ToString());
                }
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
            }
            finally
            {
                _myreader.Close();
            }

            return ret;
        }

        public static List<List<string>> Wczytaj2DData(string sqlCommandText)
        {
            var ret = new List<List<string>>();

            try
            {
                Raport.Instance.Raportuj("Wczytaj2DData = " + sqlCommandText);

                _myreader = new FbCommand(sqlCommandText, _connectionPrivData).ExecuteReader();
                while (_myreader.Read())
                {
                    ret.Add(new List<string>());
                    for (int i = 0; i < _myreader.FieldCount; i++)
                    {
                        ret[ret.Count - 1].Add(_myreader[i].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
            }
            finally
            {
                _myreader.Close();
            }

            return ret;
        }

        public static int Ustaw(string tabela)
        {
            string maxId = "0";

            try
            {
                maxId = Wczytaj("SELECT MAX(ID) AS ID FROM " + tabela);

                string sql = "SET GENERATOR NUMERACJA_" + tabela + " TO " + maxId;

                if (tabela == "FAKTURY")
                {
                    var maxNr = Wczytaj("SELECT MAX(Numer) AS Numer FROM FAKTURY");

                    Zapisz(sql, "SET GENERATOR NUMER_FAKTURY TO " + maxNr);
                }
                else
                {
                    Zapisz(sql);
                }
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
            }

            return Int32.Parse(maxId) + 1;
        }

        public static int UstawData(string tabela)
        {
            string maxId = Wczytaj2DData("SELECT MAX(ID) AS ID FROM " + tabela)[0][0];

            ZapiszData("SET GENERATOR NUMERACJA_" + tabela + " TO " + maxId);

            return Int32.Parse(maxId) + 1;
        }

        public static double LiczBilansOsob(int idOs)
        {
            double ret = 0;
            try
            {
                double oplata = Convert.ToDouble(Wczytaj("SELECT SUM(Kwota) AS Oplata FROM WPLATY WHERE ID_F IN ( SELECT ID FROM FAKTURY WHERE ID_N = '" + idOs + "')").Replace(".", ","));
                double sumaB = Convert.ToDouble(Wczytaj("SELECT SUM(Suma_B) AS Suma_B FROM FAKTURY WHERE ID_N = '" + idOs + "'").Replace(".", ","));

                ret = Math.Round(oplata - sumaB, 2);
            }
            catch (Exception ex)
            {
                Wyjatki.Error(ex);
            }
            return ret;
        }
    }
}
